﻿USE [Aprecon]
GO
/****** Object:  StoredProcedure [dbo].[Procedure_Sample]    Script Date: 03/24/2011 03:11:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
    
CREATE procedure [dbo].[Procedure_Sample]
as
begin

----------------------------------------------------------------------------
-- Declarations
----------------------------------------------------------------------------
set nocount on   
   
   declare     -- Standard variables
      @ExitCode                        int            -- Return value of this procedure.
      ,@rc                             int            -- Return code from a called SP.
      ,@RowCount                       int            -- Store the row count from an operation (@@rowcount)
      ,@TranStartedBool                tinyint        -- Flag indicating that transaction has begun.
   
   declare     -- Error and message handling variables
      @Error                           int            -- Store error codes returned by statements and procedures (@@error).
      ,@ErrorCode                      int            -- Internal error code.
      ,@RaiseMessage                   varchar(1000)  -- Creates helpful message to be raised when running.

   declare     -- ProcessLog variables
      @ProcessLogID                    int            -- ID passed back from sp_ProcessLogInit
      ,@ArgumentStr                    varchar(255)   -- Reconstructed argument list
      ,@UserName                       sysname        -- Name of caller (uses SYSTEM_USER when NULL)
      ,@ProcessRowCount                int            -- Number of rows returned to user.
      ,@RowExistsBool                  tinyint        -- Used to see if we should insert or update.
      ,@ProcessName                    sysname    
    
   declare
      @MAXCriterieDate						datetime	  
	  ,@DisneyTiXMLFailedThresholdDay		int			  -- The days we will back to get RAP from today in number
	  ,@DisneyTiXMLStartDate				datetime      -- The date when we start to generate WEX Remittance Advice Payment file in datetime
	  ,@DisneyTiXMLDaysArrival				int

	declare   -- constants
		@DEVICE							varchar(10)
		,@SHIPPINGMETHOD				varchar(10)
		,@SHIPPINGZONE					varchar(10)
		,@DISNEYTIXMLACCOUNTINGVENDOR   int
   
	if object_id('tempdb..#DisneyBookingItems') is not null
		drop table #DisneyBookingItems

	if object_id('tempdb..#DisneyBookingProductCode') is not null
		drop table #DisneyBookingProductCode

	if object_id('tempdb..#DisneyBookingProductCodeTaxSeq') is not null
		drop table #DisneyBookingProductCodeTaxSeq

	if object_id('tempdb..#DisneyOrderData') is not null
		drop table #DisneyOrderData

	if object_id('tempdb..#DisneyOrderTotal') is not null
		drop table #DisneyOrderTotal

	create table #DisneyBookingItems(
		BookingItemID			int
		,BookingID				int
		,SKUID					int
		,TranDate				datetime		
		,TranID					int
		,TripDate				datetime
		,RemittanceAdviceID		int
    )

	create table #DisneyBookingProductCode(
		BookingItemID					int
		,BookingID						int
		,TranDate						datetime		
		,TranID							int
		,TripDate						datetime
		,RemittanceAdviceID				int
		,ProductCode				    varchar(20)
		,SKUCostLogID					int
		,TransactionAmtCost				money
    )

	create table #DisneyBookingProductCodeTaxSeq(
		BookingItemID					int
		,MonetaryCalculationID			int
		,MaxBookingAmountSeqNbr			int		
    )

	create table #DisneyOrderData(
		BookingItemID					int
		,BookingID						int
		,ProductCode				    varchar(20)
		,SKUCostLog						int
		,TranDate						datetime		
		,TranID							int
		,TripDate						datetime
		,RemittanceAdviceID				int
		,price							money
		,tax							money
		,quantity						smallint
    )

	create table #DisneyOrderTotal(
		BookingItemID			int
		,OrderTotal             money
	)
  
	select 
        @ExitCode           = -100 -- Failure, this is reset at the bottom of the sproc.
		,@ProcessName		= object_name(@@procid)  
		,@DEVICE			= 'APRECON'
		,@SHIPPINGMETHOD	='WILLCALL'
		,@SHIPPINGZONE		='WILLCALL'
		,@DISNEYTIXMLACCOUNTINGVENDOR   = 94019 -- 6677
		
    select @RaiseMessage = 'Get the MaxCriterie Date'
	exec sp_RaiseMsg @pRaiseMessage = @RaiseMessage, @pProcID = @@PROCID
		
    -- get the cutoff date based on the last run of DisneyTiXMLOrderDetail
	select @MAXCriterieDate	= max(rah.APRCreateDate)
	from vDisneyTiXMLOrderDetail dtod
	join vRemittanceAdviceHeader rah
	on dtod.RemittanceAdviceID = rah.RemittanceAdviceID

	select @Error = @@error, @RowCount = @@rowcount
	if @Error <> 0 or @rc <> 0
	begin
		goto ErrorHandler
	end
	exec sp_RaiseMsg @pRaiseMessage = @RaiseMessage, @pProcID = @@PROCID, @pRowCount = @RowCount
	
	if @MAXCriterieDate is null
	begin
		exec @rc = SystemValueGet_
			@SystemValueName = 'DisneyTiXMLStartDate'
			,@SystemValueValue = @DisneyTiXMLStartDate OUTPUT

		select @Error = @@error, @RowCount = @@rowcount
		if @Error <> 0 or @rc <> 0
		begin
			select @RaiseMessage = 'Unable to retrieve Disney TiXML Start Date'
			goto ErrorHandler
		end

		set @MAXCriterieDate = @DisneyTiXMLStartDate
	end

	exec @rc = SystemValueGet_
		@SystemValueName = 'DisneyTiXMLFailedThresholdDay'
		,@SystemValueValue = @DisneyTiXMLFailedThresholdDay OUTPUT
 
	select @Error = @@error, @RowCount = @@rowcount
	if @Error <> 0 or @rc <> 0
	begin
		select @RaiseMessage = 'Unable to retrieve Disney TiXML Failed Threshold Day'
		goto ErrorHandler
	end
	
	exec @rc = SystemValueGet_
		@SystemValueName = 'DisneyTiXMLDaysArrival'
		,@SystemValueValue = @DisneyTiXMLDaysArrival OUTPUT
 
	select @Error = @@error, @RowCount = @@rowcount
	if @Error <> 0 or @rc <> 0
	begin
		select @RaiseMessage = 'Unable to retrieve Disney TiXML Days Arrival'
		goto ErrorHandler
	end
	
	select @RaiseMessage = 'Get the Disney TiXML BookingItems'
	exec sp_RaiseMsg @pRaiseMessage = @RaiseMessage, @pProcID = @@PROCID

	insert into #DisneyBookingItems(
		BookingItemID
		,BookingID
		,SKUID
		,TranDate		
		,TranID
		,TripDate
		,RemittanceAdviceID
    )
	select tsi.BookingItemID
		,tsi.BookingID
		,SKUID
        ,isnull(tsi.BookDate, tsi.CreateDate)
		,tsi.BookingItemID
		,UseDateBegin     
		,raii.RemittanceAdviceID  
	from vRemittanceAdviceHeader rah
	join vRemittanceAdviceInvoice rai
	on rah.RemittanceAdviceID = rai.RemittanceAdviceID
	join vRemittanceAdviceInvoiceItem raii
	on  rai.RemittanceAdviceID = raii.RemittanceAdviceID
	and rai.InvoiceID = raii.InvoiceID
	join vInvoiceItem ii
	on raii.InvoiceID = ii.InvoiceID
	and raii.InvoiceItemID = ii.InvoiceItemID
	join TravelShopsItem tsi
	on  ii.BookingItemID = tsi.BookingItemID
	and ii.BookingSystemID = tsi.BookingSystemID
    where  1 =1
	and rah.AccountingVendorID = @DISNEYTIXMLACCOUNTINGVENDOR   -- Disney TiXML Accounting Vendor
	and tsi.BookingSystemID = 2	                                 -- TravelShops
	and tsi.BookingItemStateID <> 2	                         -- cancelled booking is excluded
	-- @DisneyTiXMLFailedThresholdDay is the cushion day for RA create date
	and raii.APRCreateDate > dateadd(day, -@DisneyTiXMLFailedThresholdDay, @MAXCriterieDate)
	and raii.RemittanceAmt <> 0

	select @Error = @@error, @RowCount = @@rowcount
	if @Error <> 0
	begin
		goto ErrorHandler
	end
	exec sp_RaiseMsg @pRaiseMessage = @RaiseMessage, @pProcID = @@PROCID, @pRowCount = @RowCount

	select @RaiseMessage = 'Get the Disney TiXML Booking Product code'
	exec sp_RaiseMsg @pRaiseMessage = @RaiseMessage, @pProcID = @@PROCID

	insert into #DisneyBookingProductCode(
		BookingItemID
		,BookingID
		,TranDate		
		,TranID
		,TripDate
		,RemittanceAdviceID
		,ProductCode
		,SKUCostLogID
		,TransactionAmtCost		
	)
	select dbi.BookingItemID
		,dbi.BookingID
		,dbi.TranDate		
		,dbi.TranID
		,dbi.TripDate
		,dbi.RemittanceAdviceID
		,scl.VendorProductCode
		,scl.SKUCostLogID
		,tsc.TransactionAmtCost as price	
	from #DisneyBookingItems dbi
	join APReconStaging..SKUCostLog scl
	on dbi.SKUID = scl.SKUID
	join TravelShopsCost tsc
	on dbi.BookingItemID = tsc.BookingItemID
	and tsc.MonetaryCalculationID = scl.SKUCostLogID
	where scl.LogActionTypeID <> 3
	
	select @Error = @@error, @RowCount = @@rowcount	
	if @Error <> 0
	begin
		goto ErrorHandler
	end
	exec sp_RaiseMsg @pRaiseMessage = @RaiseMessage, @pProcID = @@PROCID, @pRowCount = @RowCount

	select @RaiseMessage = 'Get the Disney TiXML Booking tax amount records with the max sequence number'
	exec sp_RaiseMsg @pRaiseMessage = @RaiseMessage, @pProcID = @@PROCID

	insert into #DisneyBookingProductCodeTaxSeq(
		BookingItemID
		,MonetaryCalculationID
		,MaxBookingAmountSeqNbr
	)
	select tsctax.BookingItemID 
		,tsc.MonetaryCalculationID
		,max(tsctax.BookingAmountSeqNbr) as MaxBookingAmountSeqNbr	
	from  #DisneyBookingProductCode dbi
	join vTravelShopsCost tsc
	on dbi.BookingItemID = tsc.BookingItemID
	and dbi.SKUCostLogID = tsc.MonetaryCalculationID
	join vTravelShopsCost tsctax
	on tsc.BookingItemID = tsctax.BookingItemID
	and tsctax.MonetaryClassID = 3
	and tsctax.BookingAmountSeqNbr < tsc.BookingAmountSeqNbr
	group by tsctax.BookingItemID 
		,tsc.MonetaryCalculationID

	select @Error = @@error, @RowCount = @@rowcount
	if @Error <> 0
	begin
		goto ErrorHandler
	end
	exec sp_RaiseMsg @pRaiseMessage = @RaiseMessage, @pProcID = @@PROCID, @pRowCount = @RowCount

	select @RaiseMessage = 'Get Disney order data'
	exec sp_RaiseMsg @pRaiseMessage = @RaiseMessage, @pProcID = @@PROCID

	insert into #DisneyOrderData(
		BookingItemID	
		,BookingID
		,ProductCode				
		,SKUCostLog			
		,TranDate
		,TranID	
		,TripDate
		,RemittanceAdviceID
		,price							
		,tax							
		,quantity
	)
	select dbpc.BookingItemID
	,dbpc.BookingID
	,dbpc.ProductCode
	,dbpc.SKUCostLogID
	,dbpc.TranDate
	,dbpc.TranID
	,dbpc.TripDate
	,dbpc.RemittanceAdviceID
	,sum(dbpc.TransactionAmtCost + isnull(tsc.TransactionAmtCost,0))/ count(dbpc.SKUCostLogID)
	,sum(isnull(tsc.TransactionAmtCost,0)) /count(dbpc.SKUCostLogID)
	,count(dbpc.SKUCostLogID)
	from #DisneyBookingProductCode dbpc
	left join #DisneyBookingProductCodeTaxSeq dbpct
	on dbpc.BookingItemID = dbpct.BookingItemID
	and dbpc.SKUCostLogID = dbpct.MonetaryCalculationID
	left join TravelShopsCost tsc
	on dbpct.BookingItemID = tsc.BookingItemID
	and dbpct.MaxBookingAmountSeqNbr = tsc.BookingAmountSeqNbr
	group by dbpc.BookingItemID
		,dbpc.BookingID
		,dbpc.ProductCode
		,dbpc.SKUCostLogID
		,dbpc.TranDate
		,dbpc.TranID
		,dbpc.TripDate
		,dbpc.RemittanceAdviceID

	select @Error = @@error, @RowCount = @@rowcount
	if @Error <> 0
	begin
		goto ErrorHandler
	end
	exec sp_RaiseMsg @pRaiseMessage = @RaiseMessage, @pProcID = @@PROCID, @pRowCount = @RowCount

	select @RaiseMessage = 'Create sequence number for booking item'
	exec sp_RaiseMsg @pRaiseMessage = @RaiseMessage, @pProcID = @@PROCID

	insert into #DisneyOrderTotal(BookingItemID, OrderTotal)
	select BookingItemID, Sum(Price*quantity) as OrderTotal
	from #DisneyOrderData
	group by BookingItemID
	
	select @Error = @@error, @RowCount = @@rowcount
	if @Error <> 0
	begin
		goto ErrorHandler
	end
	exec sp_RaiseMsg @pRaiseMessage = @RaiseMessage, @pProcID = @@PROCID, @pRowCount = @RowCount

	select @RaiseMessage = 'Find out if there is any bookingitem is remitted multiple times'
	exec sp_RaiseMsg @pRaiseMessage = @RaiseMessage, @pProcID = @@PROCID
	
	------------------------------------------------------------------------------------------
    -- find if there is any Disney booking item is remitted multiple times. In theory, this  -
    -- should never happen. But in case this happens, we take the remittance id that         -
	-- remitted first.								                                         -
	------------------------------------------------------------------------------------------
	delete dod   
	from #DisneyOrderData dod
	join (
		select BookingItemID, ProductCode, min(RemittanceAdviceID) as MinRemittanceAdviceID
		from #DisneyOrderData	
		group by BookingItemID, ProductCode
	) dodmin
	on dod.BookingItemID = dodmin.BookingItemID
	and dod.ProductCode = dodmin.ProductCode
	where dod.RemittanceAdviceID <> dodmin.MinRemittanceAdviceID

	select @Error = @@error, @RowCount = @@rowcount
	if @Error <> 0
	begin
		goto ErrorHandler
	end
	exec sp_RaiseMsg @pRaiseMessage = @RaiseMessage, @pProcID = @@PROCID, @pRowCount = @RowCount

	select @RaiseMessage = 'Populate table DisneyTiXMLOrderDetail'
	exec sp_RaiseMsg @pRaiseMessage = @RaiseMessage, @pProcID = @@PROCID

	insert into DisneyTiXMLOrderDetail (
        OrderID
		,ProductCode
		--,cmdNumber
		,OrderTotal
		,TranDate		
		,TranDevice
		,TranID
		,qty
		,Price
		,PriceTax
		,ShippingMethod
		,ShippingZone
		,GuestLastName
		,GuestFirstName
		,TripDate
		,ShiptoName
		,ShiptoAddress1
		,ShiptoAddress2
		,ShiptoCity
		,ShiptoState
		,ShiptoZip
		,ShiptoCounty
		,ShiptoPhone
		,CClientNote
		,RemittanceAdviceID
		,APRCreateDate
		,APRCreateBy
		,APRUpdateDate
		,APRUpdateBy
		,USageStatusID
	)
	select 'EX' + right('00000000' + cast(dod.BookingItemID as varchar(8)),8)
		,left(dod.ProductCode,8)
		--,dods.ID as cmdNumber
		,dot.OrderTotal as OrderTotal
		,dod.TranDate
		,@DEVICE
		,dod.BookingItemID
		,dod.quantity
		,dod.price
		,dod.tax
		,@SHIPPINGMETHOD
		,@SHIPPINGZONE
		,dtbt.LastName
		,dtbt.FirstName
		,dod.TripDate
		,dtbt.FirstName + ' ' + dtbt.LastName
		,dtbt.AddressLine1
		,dtbt.AddressLine2
		,dtbt.CityName
		,dtbt.RegionName
		,dtbt.PostalCode
		,dtbt.CountryCode
		-- concatenate phone number
        ,case when rtrim(ltrim(dtbt.PhoneCountryPrefixNbrDay))='' then dtbt.PhoneCountryPrefixNbrEvening else dtbt.PhoneCountryPrefixNbrDay end
        +case when ltrim(rtrim(dtbt.PhoneCountryPrefixNbrDay)) <> '' or ltrim(rtrim(dtbt.PhoneCountryPrefixNbrEvening)) <> ''
              then '-' else '' end
        +case when rtrim(ltrim(dtbt.PhoneAreaCodeNbrDay))='' then dtbt.PhoneAreaCodeNbrEvening else dtbt.PhoneAreaCodeNbrDay end
        +case when ltrim(rtrim(dtbt.PhoneAreaCodeNbrDay)) <> '' or ltrim(rtrim(PhoneAreaCodeNbrEvening)) <> ''
              then '-' else '' end
        +case when rtrim(ltrim(dtbt.PhoneLocalNbrDay))='' then dtbt.PhoneLocalNbrEvening else dtbt.PhoneLocalNbrDay end
        +case when ltrim(rtrim(dtbt.PhoneExtensionNbrDay)) <> '' or ltrim(rtrim(dtbt.PhoneExtensionNbrEvening)) <> ''
              then ' ext.' else '' end
		,''
		,dod.RemittanceAdviceID		
		,getdate()
		,@ProcessName 
		,getdate()
		,@ProcessName 
		,1
	from #DisneyOrderData dod
	join #DisneyOrderTotal dot
	on dod.BookingItemID = dot.BookingItemID
	left join APReconStaging..DisneyTiXMLBookingTraveler dtbt
	on dod.BookingID = dtbt.BookingID
	and dtbt.BookingSystemID = 2
	and dtbt.BookingTravelerSeqNbr = 1
	left join DisneyTiXMLOrderDetail dtod
	on dod.BookingItemID = dtod.TranID
	and dod.ProductCode = dtod.ProductCode
	where dtod.OrderID is null
	order by dtod.OrderID

	select @Error = @@error, @RowCount = @@rowcount
	if @Error <> 0
	begin
		goto ErrorHandler
	end
	exec sp_RaiseMsg @pRaiseMessage = @RaiseMessage, @pProcID = @@PROCID, @pRowCount = @RowCount

	----------------------------------------------------------------------------
	-- Set ExitCode properly
	----------------------------------------------------------------------------
	select @ExitCode = 0  -- Success
	goto ExitProc
   
	----------------------------------------------------------------------------
	-- Error Handler
	----------------------------------------------------------------------------
	ErrorHandler:
       
		-- Print the Error Message now that will kill isql.
		exec sp_RaiseMsg
                @pRaiseMessage = @RaiseMessage
               ,@pProcID = @@PROCID
               ,@pRowCount = @RowCount
               ,@pError = @Error
               ,@pSeverity = 16
               ,@pState = 1

		goto ExitProc
   
	----------------------------------------------------------------------------
	-- Exit Procedure
	----------------------------------------------------------------------------
	ExitProc:

		return (@ExitCode)
end
